import sqlite3

conn = sqlite3.connect('app.db')
cursor = conn.cursor()

create_table_sql_1 = '''
CREATE TABLE "os_storage_files" (
   "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
   "size" integer NOT NULL,
   "dir" text NOT NULL,
   "filename" text NOT NULL,
   "ext" varchar ( 8 ) NOT NULL,
   "sign" varchar ( 100 ) NOT NULL,
   "num" integer NOT NULL DEFAULT 0,
   "ctime" varchar NOT NULL DEFAULT '',
   "face_num" integer NOT NULL DEFAULT 0,
   "ctg" integer NOT NULL DEFAULT 0,
CONSTRAINT "u_sign" UNIQUE ( "sign" ASC )
);
'''

create_table_sql_2 = '''
CREATE TABLE "os_storage_duplicate_files" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "size" integer NOT NULL,
    "dir" text NOT NULL,
    "filename" text NOT NULL,
    "sign" varchar ( 100 ) NOT NULL,
CONSTRAINT "u_fp" UNIQUE ( "dir", "filename" )
);
'''

create_table_sql_3 = '''
CREATE TABLE "img_dict" ( "id" integer NOT NULL, "path" text, "keyword" varchar ( 20 ), "created_at" datetime, PRIMARY KEY ( "id" ) );
'''

create_table_sql_4 = '''
CREATE TABLE "img_recognize_results" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "img_dict_id" integer,
    "img_file_id" integer,
    CONSTRAINT "u_recog" UNIQUE ("img_dict_id" ASC, "img_file_id" ASC)
);
'''


def create_table(sql):
    try:
        cursor.execute(sql)
        conn.commit()
    except Exception:
        pass


create_table(create_table_sql_1)
create_table(create_table_sql_2)
create_table(create_table_sql_3)
create_table(create_table_sql_4)

conn.close()


''' 查询已识别人脸图片
SELECT
    f.dir || '\' || f.filename file_path,
    f.face_num,
    group_concat( d.keyword ) people
FROM
    os_storage_files f
    LEFT JOIN img_recognize_results r ON r.img_file_id = f.id
    LEFT JOIN img_dict d ON d.id = r.img_dict_id
WHERE
    d.keyword IS NOT NULL
GROUP BY
    f.id;
'''
